package com.bds.wm.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bds.wm.model.procmodel.*;
import com.bds.wm.model.view.KeyBrand;
import com.bds.wm.model.view.SubCategoryList;
import com.bds.wm.utils.FormatUils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.nutz.dao.Sqls;
import org.nutz.dao.impl.NutDao;
import org.nutz.dao.sql.Sql;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: kevin
 * @date: 2018/5/18
 * @description:
 */
@Service
public class CityViewService {
    private Logger LOGGER = Logger.getLogger(CityViewService.class);
    @Resource
    private NutDao dao;

    public CityViewResultByCity getSummaryByCity(String cityName,Integer startMonth, Integer endMonth) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyCity @cityName,@startMonth,@endMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(CityViewResultByCity.class));
        sql.params().set("cityName", cityName);
        sql.params().set("startMonth", startMonth);
        sql.params().set("endMonth", endMonth);
        dao.execute(sql);
        return sql.getObject(CityViewResultByCity.class);
    }

    public List<OverviewResultbyChannelTopCity> getChannelPerByCity(String cityName, Integer startMonth, Integer endMonth) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyChannelTest @startMonth,@endMonth,@cityName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyChannelTopCity.class));
        sql.params().set("cityName", cityName);
        sql.params().set("startMonth", startMonth);
        sql.params().set("endMonth", endMonth);
        dao.execute(sql);
        return sql.getList(OverviewResultbyChannelTopCity.class);
    }

    public List<OverviewResultbyCityBrand> getOverviewResultbyCityBrand(String city, Integer start, Integer end,String subCategoryName,String brandName) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyCityBrand @CityName,@SubCategoryName,@BrandName,@StartMonth,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCityBrand.class));
        sql.params().set("CityName", city); //设置入参
        sql.params().set("SubCategoryName",subCategoryName ); //设置入参
        sql.params().set("BrandName",brandName ); //设置入参
        sql.params().set("StartMonth", start); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        List<OverviewResultbyCityBrand> brands = sql.getList(OverviewResultbyCityBrand.class);
        brands.forEach(brand->{
            brand.setBrandSalesValue(FormatUils.formatConv(brand.getBrandSalesValue(),10000));// ---销售额(万元)
            brand.setBrandTC(FormatUils.formatConv(brand.getBrandTC(),10000));// ---客单数(万笔)
            brand.setBrandStoreNo(String.valueOf(Double.valueOf(brand.getBrandStoreNo()).intValue()));// --店铺数(家)
            brand.setBrandTA(FormatUils.formatConv(brand.getBrandTA()));//---客单价(元)
            if(brand.getBrandTCCAGR()!=null) {
                brand.setBrandTCCAGR(FormatUils.formatConv(Double.valueOf(brand.getBrandTCCAGR())*100));// ---客单数增长率
            }
            if(brand.getStoreTA()!=null) {
                brand.setStoreTA(FormatUils.formatConv(brand.getStoreTA()));// ---店均客单价
            }
        });
        return brands;
    }
    public List<OverviewResultbyCityChannel> getOverviewResultbyCityChannel(String city, Integer start_time, Integer end_time) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyCityChannel @CityName,@StartMonth,@EndMonth");
            sql.setCallback(Sqls.callback.entities());
            sql.setEntity(dao.getEntity(OverviewResultbyCityChannel.class));
            sql.params().set("CityName", city); //设置入参
            sql.params().set("StartMonth", start_time); //设置入参
            sql.params().set("EndMonth", end_time); //设置入参
        dao.execute(sql);
        List<OverviewResultbyCityChannel> res = sql.getList(OverviewResultbyCityChannel.class);
        res.forEach(obj->{
            obj.setChannelGMV(FormatUils.formatConv(obj.getChannelGMV(),(10000*10000)));//销售额(亿元)
            obj.setChannelTA(FormatUils.formatConv(obj.getChannelTA()));
            obj.setChannelTC(FormatUils.formatConv(obj.getChannelTC(),10000));//客单数（万笔）
            if(obj.getChannelStoreNo()!=null) {//店铺数(万家)
                obj.setChannelStoreNo(FormatUils.formatConv(obj.getChannelStoreNo(),10000));
            }
        });
        return res;
    }

    public List<OverviewResultbyCitySuCategory> getOverviewResultbyCitySuCategory(String city, Integer start, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultofSubTier @CityName,@StartMonth,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("CityName", city); //设置入参
        sql.params().set("StartMonth", start); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }
    public List<OverviewResultbyCitySuCategory> getOverviewResultbyCitySubTier(String cityTier, Integer start, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultofSubTier @Segment,@StartMonth,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("Segment", cityTier); //设置入参
        sql.params().set("StartMonth", start); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }


    /**
     * 返回12个月的统计数据  按subCategory
     * @param city
     * @param suCategory
     * @param end
     * @return
     */
    public List<OverviewResultbyCitySuCategory> getOverviewResultTrendbyCitySuCategory(String city, String suCategory, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTrendofSuTier @CityName,@SubCategoryName,@EndMonth");
            sql.setCallback(Sqls.callback.entities());
            sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
            sql.params().set("CityName", city); //设置入参
            sql.params().set("SubCategoryName", suCategory); //设置入参
            sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }
    /**
     * 返回12个月的统计数据 按category
     * @param city
     * @param category
     * @param end
     * @return
     */
    public List<OverviewResultbyCitySuCategory> getOverviewResultTrendbyCityCategory(String city, String category, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTrendofCaTier @CityName,@CategoryName,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("CityName", city); //设置入参
        sql.params().set("CategoryName", category); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }
    /**
     * 返回12个月的统计数据 按section
     * @param city
     * @param section
     * @param end
     * @return
     */
    public List<OverviewResultbyCitySuCategory> getOverviewResultTrendbyCitySection(String city, String section, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTrendofSeTier @CityName,@SectionName,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("CityName", city); //设置入参
        sql.params().set("SectionName", section); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }


    /**
     * 返回12个月的统计数据  按subCategory
     * @param city
     * @param suCategory
     * @param end
     * @return
     */
    public List<OverviewResultbyCitySuCategory> getOverviewResultTrendbyCityCaTier(String city, String suCategory, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTrendofCaTier @CityTier,@SubCategoryName,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("CityTier", city); //设置入参
        sql.params().set("SubCategoryName", suCategory); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }
    /**
     * 返回12个月的统计数据 按category
     * @param city
     * @param category
     * @param end
     * @return
     */
    public List<OverviewResultbyCitySuCategory> getOverviewResultTrendbyCitySuTier(String city, String category, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTrendofSuTier @CityTier,@CategoryName,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("CityTier", city); //设置入参
        sql.params().set("CategoryName", category); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }
    /**
     * 返回12个月的统计数据 按section
     * @param city
     * @param section
     * @param end
     * @return
     */
    public List<OverviewResultbyCitySuCategory> getOverviewResultTrendbyCitySeTier(String city, String section, Integer end) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTrendofSeTier @CityTier,@SectionName,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySuCategory.class));
        sql.params().set("CityTier", city); //设置入参
        sql.params().set("SectionName", section); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultbyCitySuCategory.class);
    }


    /**
     * 拼接前台模型 店铺品类销售情况 表统计数据
     * @param cates
     * @return
     */
    public JSONObject groupByCategory(List<OverviewResultbyCitySuCategory> cates){
        JSONObject json=new JSONObject();
        Map<String,String[]> cateMap=new HashMap<>();//分组
        Map<String,List<String[]>> subCateMap=new HashMap<>();//分组
        cates.forEach(cate->{
            try {
                if(StringUtils.isNotEmpty(cate.getSectionName())&&!"Null".equals(cate.getSectionName())) {
                    BigDecimal tc_agr = BigDecimal.ZERO;
                    BigDecimal tc_per = BigDecimal.ZERO;
                    BigDecimal sale = BigDecimal.ZERO;
                    if (cate.getSuCategoryTCCAGR() != null) {
                        tc_agr = new BigDecimal(cate.getSuCategoryTCCAGR()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getSuCategoryTCPer() != null) {
                        tc_per = new BigDecimal(cate.getSuCategoryTCPer()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getSubCategorySalesValuePer() != null) {
                        sale = new BigDecimal(cate.getSubCategorySalesValuePer()).multiply(BigDecimal.valueOf(100));
                    }
                    String[] subRow = new String[]{
                            cate.getSubCategoryName(),//21子 细分品类
                            FormatUils.formatConv(Double.valueOf(cate.getSuCategoryStoreNo()).intValue()),//22细分品类店铺数
                            FormatUils.formatConv(cate.getSuCategoryTC(), 10000),//23细分品类客单数(万）
                            FormatUils.formatConv(tc_per.doubleValue()),//24细分品类客单占比
                            FormatUils.formatConv(tc_agr.doubleValue()),//25细分品类客单数增长率
                            FormatUils.formatConv(Double.valueOf(cate.getSuCategoryTCYoY())*100),// new add    客单数同比
                            FormatUils.formatConv(cate.getSubCategorySalesValue(), (10000 * 10000)),//26细分品类销售额(亿元）
                            FormatUils.formatConv(sale.doubleValue()),//27细分品类销售额占比
                            FormatUils.formatConv(cate.getSuCategoryTA())//28细分品类客单价

                    };
                    List<String[]> subRows = null;
                    if (!cateMap.containsKey(cate.getCategoryName())) {
                        subRows = new ArrayList<>();
                        BigDecimal c_tc_per = BigDecimal.ZERO;
                        BigDecimal c_tc_agr = BigDecimal.ZERO;
                        BigDecimal c_sale = BigDecimal.ZERO;
                        if (cate.getCategoryTCPer() != null) {
                            c_tc_per = new BigDecimal(cate.getCategoryTCPer()).multiply(BigDecimal.valueOf(100));
                        }
                        if (cate.getCategoryTCCAGR() != null) {
                            c_tc_agr = new BigDecimal(cate.getCategoryTCCAGR()).multiply(BigDecimal.valueOf(100));
                        }
                        if (cate.getCategorySalesValuePer() != null) {
                            c_sale = new BigDecimal(cate.getCategorySalesValuePer()).multiply(BigDecimal.valueOf(100));
                        }
                        String[] row = new String[]{
                                cate.getSectionName(), //11.品类
                                cate.getCategoryName(),//12细分品类
                                FormatUils.formatConv(Double.valueOf(cate.getCategoryStoreNo()).intValue()),//13品类店铺数
                                FormatUils.formatConv(cate.getCategoryTC(), 10000),//14品类客单数(万)
                                FormatUils.formatConv(c_tc_per.doubleValue()),//15品类客单数占比
                                FormatUils.formatConv(c_tc_agr.doubleValue()),//16品类客单数增长率
                                FormatUils.formatConv(Double.valueOf(cate.getCategoryTCYoY())*100),// new add    客单数同比
                                FormatUils.formatConv(cate.getCategorySalesValue(), (10000 * 10000)),//17品类销售额（亿元）
                                FormatUils.formatConv(c_sale.doubleValue()),//18品类销售额占比
                                FormatUils.formatConv(cate.getCategoryTA())//19品类客单价

                        };
                        cateMap.put(cate.getCategoryName(), row);
                    } else {
                        subRows = subCateMap.get(cate.getCategoryName());
                    }
                    subRows.add(subRow);
                    subCateMap.put(cate.getCategoryName(), subRows);
                }
            }catch (Exception e){
                LOGGER.warn("[service]-[33]-groupByCategory获取品类信息异常", e);
            }
        });
        json.put("cate",cateMap.values());
        json.put("subCate",subCateMap);
        return json;
    }

    /**
     * 拼接前台模型 店铺品类销售情况 12个月的图统计数据
     * @param cates
     * @return
     */
    public JSONObject groupChart(List<OverviewResultbyCitySuCategory> cates){
        JSONObject json=new JSONObject();
        Map<String,String[]> cateMap=new HashMap<>();//分组
        Map<String,List<String[]>> subCateMap=new HashMap<>();//分组
        cates.forEach(cate->{
            try {
                if(StringUtils.isNotEmpty(cate.getSectionName())&&!"Null".equals(cate.getSectionName())) {
                    BigDecimal tc_agr = BigDecimal.ZERO;
                    BigDecimal tc_per = BigDecimal.ZERO;
                    BigDecimal sale = BigDecimal.ZERO;
                    if (cate.getSuCategoryTCCAGR() != null) {
                        tc_agr = new BigDecimal(cate.getSuCategoryTCCAGR()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getSuCategoryTCPer() != null) {
                        tc_per = new BigDecimal(cate.getSuCategoryTCPer()).multiply(BigDecimal.valueOf(100));
                    }
                    if (cate.getSubCategorySalesValuePer() != null) {
                        sale = new BigDecimal(cate.getSubCategorySalesValuePer()).multiply(BigDecimal.valueOf(100));
                    }
                    String[] subRow = new String[]{
                            cate.getSubCategoryName(),//21子 细分品类
                            FormatUils.formatConv(cate.getSuCategoryStoreNo(), 10000),//22细分品类店铺数(万）
                            FormatUils.formatConv(cate.getSuCategoryTC(), 10000),//23细分品类客单数(万）
                            FormatUils.formatConv(tc_per.doubleValue()),//24细分品类客单占比
                            FormatUils.formatConv(tc_agr.doubleValue()),//25细分品类客单数增长率
                            FormatUils.formatConv(cate.getSubCategorySalesValue(), (10000 * 10000)),//26细分品类销售额(亿元）
                            FormatUils.formatConv(sale.doubleValue()),//27细分品类销售额占比
                            FormatUils.formatConv(cate.getSuCategoryTA())//28细分品类客单价
                    };
                    List<String[]> subRows = null;
                    if (!cateMap.containsKey(cate.getCategoryName())) {
                        subRows = new ArrayList<>();
                        BigDecimal c_tc_per = BigDecimal.ZERO;
                        BigDecimal c_tc_agr = BigDecimal.ZERO;
                        BigDecimal c_sale = BigDecimal.ZERO;
                        if (cate.getCategoryTCPer() != null) {
                            c_tc_per = new BigDecimal(cate.getCategoryTCPer()).multiply(BigDecimal.valueOf(100));
                        }
                        if (cate.getCategoryTCCAGR() != null) {
                            c_tc_agr = new BigDecimal(cate.getCategoryTCCAGR()).multiply(BigDecimal.valueOf(100));
                        }
                        if (cate.getCategorySalesValuePer() != null) {
                            c_sale = new BigDecimal(cate.getCategorySalesValuePer()).multiply(BigDecimal.valueOf(100));
                        }
                        String[] row = new String[]{
                                cate.getSectionName(), //11.品类
                                cate.getCategoryName(),//12细分品类
                                FormatUils.formatConv(cate.getCategoryStoreNo(), 10000),//13品类店铺数(万)
                                FormatUils.formatConv(cate.getCategoryTC(), 10000),//14品类客单数(万)
                                FormatUils.formatConv(c_tc_per.doubleValue()),//15品类客单数占比
                                FormatUils.formatConv(c_tc_agr.doubleValue()),//16品类客单数增长率
                                FormatUils.formatConv(cate.getCategorySalesValue(), (10000 * 10000)),//17品类销售额（亿元）
                                FormatUils.formatConv(c_sale.doubleValue()),//18品类销售额占比
                                FormatUils.formatConv(cate.getCategoryTA())//19品类客单价
                        };
                        cateMap.put(cate.getCategoryName(), row);
                    } else {
                        subRows = subCateMap.get(cate.getCategoryName());
                    }
                    subRows.add(subRow);
                    subCateMap.put(cate.getCategoryName(), subRows);
                }
            }catch (Exception e){
                LOGGER.warn("[service]-[33]-groupChart获取店铺品类销售情况异常", e);
            }
        });

        json.put("cate",cateMap.values());
        json.put("subCate",subCateMap);
        return json;
    }






    public JSONObject getMonthlyByCityChannel(String city, Integer endMonth, Integer months) {
        Sql sql = Sqls.fetchEntity("exec Report.getMonthlyResultbyChannel @EndMonth,@Months,@Segment");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(MonthlyResultByChannel.class));
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("Months", months); //设置入参
        sql.params().set("Segment", city); //设置入参
        dao.execute(sql);
        List<MonthlyResultByChannel> list = sql.getList(MonthlyResultByChannel.class);
        if (list==null||list.size()==0)
            return null;

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("BaiduWaiMai", new JSONArray());
        jsonObject.put("ELeMe", new JSONArray());
        jsonObject.put("MeiTuan", new JSONArray());
        //按平台维度合并数据
        for (MonthlyResultByChannel m : list) {
            JSONObject tmp = new JSONObject();
            String cityName=m.getSubSegment();
            Long yearMonth = m.getYearMonth();
            Long channelID=m.getSubSegmentID();
            String channelName = m.getSubSegment();
            Double totStoreNo=m.getChannelStoreNo();
            Double totSalesValue=m.getChannelGMV();
            Double totTC = m.getChannelTC();

            tmp.put("cityName",cityName);
            tmp.put("yearMonth", yearMonth);
            tmp.put("channelID",channelID);
            tmp.put("totStoreNo",totStoreNo);
            tmp.put("totSalesValue",totSalesValue);
            tmp.put("totTC",totTC);

            if (channelName.toLowerCase().equals("baiduwaimai")) {
                jsonObject.getJSONArray("BaiduWaiMai").add(tmp);
            } else if (channelName.toLowerCase().equals("eleme")) {
                jsonObject.getJSONArray("ELeMe").add(tmp);
            } else {
                jsonObject.getJSONArray("MeiTuan").add(tmp);
            }
        }
        return jsonObject;
    }

    public CAGRByCity getCAGRByCity(String city_name, Integer startMonth, Integer endMonth) {
        Sql sql = Sqls.fetchEntity("exec Report.getCAGROverviewResultbyCity @CityName,@EndMonth,@StartMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(CAGRByCity.class));
        sql.params().set("CityName", city_name);
        sql.params().set("EndMonth", endMonth);
        sql.params().set("StartMonth", startMonth);
        dao.execute(sql);
        List<CAGRByCity> list = sql.getList(CAGRByCity.class);
        if(list!=null&&list.size()!=0){
            return list.get(0);
        }else {
            return null;
        }
    }
    /**
     *
     * 促销信息
     * @param city
     * @return
     */
    public List<PromotionInfo> getPromotionInfobyCity(String city, Integer start, Integer end, String brandName) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getPromotionInfobyCity @CityName,@BrandName,@StartMonth,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(PromotionInfo.class));
        sql.params().set("CityName", city); //设置入参
        sql.params().set("StartMonth", start); //设置入参
        sql.params().set("EndMonth", end); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        dao.execute(sql);
        return sql.getList(PromotionInfo.class);
    }

    /**
     * 促销信息  品牌下拉框
     * @return
     */
    public List<KeyBrand> getKeyBrands() {
        return dao.query(KeyBrand.class, null);
    }

    public List<CityMapOverviewResult> getCityMapOverviewResult(Integer startMonth, Integer endMonth, String city_name, String brand_name, String subcategory, String max_lng, String min_lng, String max_lat, String min_lat) {
        Sql sql = Sqls.fetchEntity("exec Report.getCityMapOverviewResult @StartMonth,@EndMonth,@CityName,@subcategory,@BrandName," +
                "@MinLongitude,@MaxLongitude,@MinLatitude,@MaxLatitude");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(CityMapOverviewResult.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityName", city_name); //设置入参
        sql.params().set("subcategory", subcategory); //设置入参
        sql.params().set("BrandName", brand_name); //设置入参
        sql.params().set("MaxLongitude", max_lng); //设置入参
        sql.params().set("MinLongitude", min_lng); //设置入参
        sql.params().set("MaxLatitude", max_lat); //设置入参
        sql.params().set("MinLatitude", min_lat); //设置入参
        dao.execute(sql);
        List<CityMapOverviewResult> list=sql.getList(CityMapOverviewResult.class);
        return list;
    }

    public RatebyCitySub getRatebyCitySub(String city_name, Integer startMonth, Integer endMonth, String subcategory) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultRatebyCitySub @startMonth,@endMonth,@cityName,@SubCategory");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(RatebyCitySub.class));
        sql.params().set("cityName", city_name);
        sql.params().set("startMonth", startMonth);
        sql.params().set("endMonth", endMonth);
        sql.params().set("SubCategory", subcategory);
        dao.execute(sql);
        return sql.getObject(RatebyCitySub.class);
    }

    public OverviewResultbyTopCityRaking getTopCityRaking(String city_name, Integer startMonth, Integer endMonth) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyTopCityRaking @cityName,@startMonth,@endMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyTopCityRaking.class));
        sql.params().set("cityName", city_name);
        sql.params().set("startMonth", startMonth);
        sql.params().set("endMonth", endMonth);
        dao.execute(sql);
        return sql.getObject(OverviewResultbyTopCityRaking.class);
    }

    public YoYOverviewResultbyCity getYoYOverviewResultbyCity(String city_name, Integer startMonth, Integer endMonth,String selector) {
        Sql sql = Sqls.fetchEntity("exec Report.getYoYOverviewResultbyCity @cityName,@startMonth,@endMonth,@selector");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(YoYOverviewResultbyCity.class));
        sql.params().set("cityName", city_name);
        sql.params().set("startMonth", startMonth);
        sql.params().set("endMonth", endMonth);
        sql.params().set("selector", selector);
        dao.execute(sql);
        return sql.getObject(YoYOverviewResultbyCity.class);
    }

    public List<CityBrandGrowthRate> getCityBrandGrowthRate(String cityName, Integer startMonth, Integer endMonth, String subCategoryName) {
        Sql sql = Sqls.fetchEntity("exec Report.getCityBrandGrowthRate " +
                "@CityName,@SubCategoryName,@StartMonth,@EndMonth");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(CityBrandGrowthRate.class));
        sql.params().set("CityName", cityName);
        sql.params().set("SubCategoryName", subCategoryName);
        sql.params().set("StartMonth", startMonth);
        sql.params().set("EndMonth", endMonth);
        dao.execute(sql);
        return sql.getList(CityBrandGrowthRate.class);
    }
    public CityBrandGrowthLogo getCityBrandGrowthLogo(String brandName) {
        Sql sql = Sqls.fetchEntity("exec Report.getCityBrandGrowthLogo " +
                "@BrandName");
        sql.setCallback(Sqls.callback.entity());
        sql.setEntity(dao.getEntity(CityBrandGrowthLogo.class));
        sql.params().set("BrandName", brandName);
        dao.execute(sql);
        return sql.getObject(CityBrandGrowthLogo.class);
    }


    public List<SubCategoryList> getSubCategoryNames(){
        return dao.query(SubCategoryList.class, null);
    }

    public OverviewResultbyCitySub getOverviewResultbyCitySub(int start_month,int end_month, String city_name,String subcate) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyCitySub " +
                "@StartMonth,@EndMonth,@CityName,@SubCategoryName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyCitySub.class));
        sql.params().set("CityName", city_name);
        sql.params().set("SubCategoryName", subcate);
        sql.params().set("StartMonth", start_month);
        sql.params().set("EndMonth", end_month);
        dao.execute(sql);
        return sql.getObject(OverviewResultbyCitySub.class);
    }

    public TopCityRakingofSubcategory getTopCityRakingofSubcategory(Integer startMonth, Integer endMonth, String city_name, String subcate) {
        Sql sql = Sqls.fetchEntity("exec Report.getTopCityRakingofSubcategory " +
                "@CityName,@StartMonth,@EndMonth,@SubCategoryName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(TopCityRakingofSubcategory.class));
        sql.params().set("CityName", city_name);
        sql.params().set("SubCategoryName", subcate);
        sql.params().set("StartMonth", startMonth);
        sql.params().set("EndMonth", endMonth);
        dao.execute(sql);
        return sql.getObject(TopCityRakingofSubcategory.class);
    }

    public CityConsumption getOverviewResultbyCityConsumption(String city_name) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyCityConsumption @CityName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(CityConsumption.class));
        sql.params().set("CityName", city_name);
        dao.execute(sql);
        return sql.getObject(CityConsumption.class);
    }
}






























































































































































